package com.flong.gui.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQLServer {
	
	private String forName="com.microsoft.jdbc.sqlserver.SQLServerDriver";
	private String driverManager="jdbc:microsoft:sqlserver://localhost:1433;databaseName=test";
	private String userName="sa";
	private String passWord="sa";
	
	public static String[][] dataValues = null;

	public Connection connSql() {
		Connection conn = null;
		try {
			Class.forName(forName);
			conn = DriverManager.getConnection(driverManager, userName, passWord);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	public int sqlCont() {
		int x = 0;
		try {
			Connection conn = connSql();
			String sql = "select * from stu1";
			Statement sta = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			ResultSet re = sta.executeQuery(sql);
			re.last();
			x = re.getRow();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return x;
	}

	public String[][] selectSql(int x, int y) {
		dataValues = new String[x][3];
		int count = 0;
		Connection conn = connSql();
		String sql = "select top " + x + " * from stu1 where id  not in (select top " + y * 3 + " id from stu1 order by id )order by id";
		try {
			Statement sta = conn.createStatement();
			ResultSet re = sta.executeQuery(sql);
			while (re.next()) {
				dataValues[count][0] = String.valueOf((re.getInt(1)));
				dataValues[count][1] = re.getString(2);
				dataValues[count][2] = re.getString(3);
				count++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return dataValues;
	}
}